﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;

namespace CodeGenerate.Data
{
    /// <summary>
    /// SQLServer数据库访问类
    /// </summary>
    public class SQLHelper
    {
        /// <summary>
        /// 测试连接
        /// </summary>
        /// <param name="sConnString"></param>
        /// <returns></returns>
        public static bool TestConn(string sConnString)
        {
            bool isConn = false;
            using (SqlConnection conn = new SqlConnection(sConnString))
            {
                try
                {
                    conn.Open();
                    isConn = true;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return isConn;
        }

        #region ExecuteNonQuery
        /// <summary>
        /// 执行 普通SQL语句 访问影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paraList"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sConnString, string sql, SqlParameter[] paraList)
        {
            return ExecuteNonQuery(sConnString, sql, CommandType.Text, paraList);
        }

        /// <summary>
        /// 执行普通 SQL语句/存储过程 访问影响行数
        /// </summary>
        /// <param name="sql">存储过程/sql语句</param>
        /// <param name="type">SQL语句类型</param>
        /// <param name="paraList">参数列表</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sConnString, string sql, CommandType type, SqlParameter[] paraList)
        {
            int n = 0;
            using (SqlConnection conn = new SqlConnection(sConnString))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = type;
                //参数处理
                if ((paraList != null) && (paraList.Length > 0))
                {
                    cmd.Parameters.AddRange(paraList);
                }
                try
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    n = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception("数据库执行错误或者连接无法打开,请查看错误日志!"+ex.Message);
                }
            }
            return n;
        }
        #endregion

        #region ExecuteDataReader
        /// <summary>
        /// 执行SQL连接模式Select
        /// </summary>
        /// <param name="strSql">存储过程/sql语句</param>
        /// <param name="type"></param>
        /// <param name="paraList"></param>
        /// <returns>执行完后 SqlDataReader .Close()关闭连接</returns>
        public static SqlDataReader ExecuteDataReader(string sConnString, string strSql, CommandType type, SqlParameter[] paraList)
        {
            SqlConnection conn = new SqlConnection(sConnString);
            SqlCommand cmd = new SqlCommand(strSql, conn);
            cmd.CommandType = type;

            SqlDataReader dr = null;
            //参数处理
            if ((paraList != null) && (paraList.Length > 0))
            {
                cmd.Parameters.AddRange(paraList);
            }
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
               throw new Exception("数据库执行错误或者连接无法打开,请查看错误日志!"+ex.Message);
            }
            return dr;
        }

        /// <summary>
        /// 执行普通Sql语句 连接模式
        /// </summary>
        /// <param name="strSql">普通SQl语句</param>
        /// <param name="paraList"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteDataReader(string sConnString, string strSql, SqlParameter[] paraList)
        {
            return ExecuteDataReader(sConnString, strSql, CommandType.Text, paraList);
        }

        #endregion

        #region ExecuteDataSet
        /// <summary>
        /// 断开模式返回DataSet
        /// </summary>
        /// <param name="strSql">存储过程/sql语句</param>
        /// <param name="type"></param>
        /// <param name="paraList"></param>
        /// <param name="sTableName"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string sConnString, string strSql, CommandType type, SqlParameter[] paraList, string sTableName)
        {
            SqlConnection conn = new SqlConnection(sConnString);
            SqlCommand cmd = new SqlCommand(strSql, conn);
            cmd.CommandType = type;

            //参数处理
            if ((paraList != null) && (paraList.Length > 0))
            {
                cmd.Parameters.AddRange(paraList);
            }

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                if (sTableName != string.Empty)
                {
                    da.Fill(ds, sTableName);
                }
                else
                {
                    da.Fill(ds);
                }
            }
            catch (Exception ex)
            {
               throw new Exception("数据库执行错误或者连接无法打开,请查看错误日志!"+ex.Message);
            }
            return ds;
        }

        /// <summary>
        /// 断开模式 返回DataSet
        /// </summary>
        /// <param name="strSql">存储过程/sql语句</param>
        /// <param name="type"></param>
        /// <param name="paraList"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string sConnString, string strSql, CommandType type, SqlParameter[] paraList)
        {
            return ExecuteDataSet(sConnString, strSql, type, paraList, string.Empty);
        }

        /// <summary>
        /// 断开模式 返回DataSet
        /// </summary>
        /// <param name="strSql">普通SQL语句</param>
        /// <param name="paraList"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string sConnString, string strSql, SqlParameter[] paraList)
        {
            return ExecuteDataSet(sConnString, strSql, CommandType.Text, paraList);
        }
        #endregion
    } 
}
